<?php
defined( "AZ_EXE_" ) or die ( "CORE VARIABLE IS NOT DEFINED." );
	class UserModel extends Model { 
		public function display( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("first_name","last_name","email") , $sortColumn="first_name" , $sortCriteria="ASC" ){
			
			$start = is_numeric($start)&&$start>0?$start:1;
			$perpage = is_numeric($perpage)&&$perpage>0?$perpage:20;
			
			$result = array();
			$query = "SELECT users.id as user_id , first_name , last_name , email , type  
			FROM users
			" ;
			if ( $searchString != "" ){
				$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("first_name","last_name","email") : $searchColumns ; 
				if ( is_array($searchColumns) && !empty($searchColumns) ){
				$conditions = "" ;
					$conditions = array() ;
					foreach( $searchColumns AS $columnName ){
						$conditions[] = " $columnName LIKE '%$searchString%' " ;
					}
					$conditions = " WHERE (" . implode("OR", $conditions ) . ") AND ( type = 3 ) ";
					$sortColumn = $sortColumn===""||$sortColumn===false?"first_name":$sortColumn;
					$sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
					$query .= "$conditions " ;
				}
			}else{
				$query .= " WHERE type = 3 " ;
			}
			if( $sortColumn != "" || $sortColumn != false ){
				$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"ASC";
				$query .= " ORDER BY $sortColumn $sortCriteria " ;
			}
			$queryGetRow = $query ;
            $total = ceil($this->getRowWithQuery($queryGetRow)/$perpage);
			$query .= " LIMIT ".(($start-1)*$perpage)." , ".$perpage."; " ;
			return array("page"=>$start,"total"=>$total,"users"=>$this->executeQueryAsObject( $query ));
		}

        public function displayPremium( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("first_name","last_name","email") , $sortColumn="first_name" , $sortCriteria="ASC" ){
            
            $start = is_numeric($start)&&$start>0?$start:1;
            $perpage = is_numeric($perpage)&&$perpage>0?$perpage:20;
            
            $result = array();
            $query = "SELECT users.id as user_id , first_name , last_name , email , type , label , price , start_date, end_date 
            FROM users , expiredates , userexpireds
            WHERE users.id=userexpireds.user_id
            AND   expiredates.id=userexpireds.expire_id  
            " ;
            if ( $searchString != "" ){
                $searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("first_name","last_name","email") : $searchColumns ; 
                if ( is_array($searchColumns) && !empty($searchColumns) ){
                $conditions = "" ;
                    $conditions = array() ;
                    foreach( $searchColumns AS $columnName ){
                        $conditions[] = " $columnName LIKE '%$searchString%' " ;
                    }
                    $conditions = " AND (" . implode("OR", $conditions ) . ") AND type = 2  ";
                    $sortColumn = $sortColumn===""||$sortColumn===false?"first_name":$sortColumn;
                    $sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
                    $query .= "$conditions " ;
                }
            }else{
                $query .= " AND type = 2 " ;
            }
            if( $sortColumn != "" || $sortColumn != false ){
                $sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"ASC";
                $query .= " ORDER BY $sortColumn $sortCriteria " ;
            }
            $queryGetRow = $query ;
            $total = ceil($this->getRowWithQuery($queryGetRow)/$perpage);
            $query .= " LIMIT ".(($start-1)*$perpage)." , ".$perpage."; " ;
            return array("page"=>$start,"total"=>$total,"users"=>$this->executeQueryAsObject( $query ));
        }
        public function displayActivity( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("first_name","last_name","email") , $sortColumn="first_name" , $sortCriteria="ASC" ){
        		
        	$start = is_numeric($start)&&$start>0?$start:1;
        	$perpage = is_numeric($perpage)&&$perpage>0?$perpage:20;
        		
        	$result = array();
        	$query = "SELECT u.`id` as user_id , u.`first_name` , u.`last_name` , u.`email` , ua.`id` as a_id, ua.`ref_number`, ua.`activities`, ua.`created` 
			FROM users as u INNER JOIN `user_reference_logs` as ua ON u.`id` = ua.`user_id`
			" ;
        	if ( $searchString != "" ){
        		$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("first_name","last_name","email") : $searchColumns ;
        		if ( is_array($searchColumns) && !empty($searchColumns) ){
        			$conditions = "" ;
        			$conditions = array() ;
        			foreach( $searchColumns AS $columnName ){
        				$conditions[] = " $columnName LIKE '%$searchString%' " ;
        			}
        			$conditions = " WHERE (" . implode("OR", $conditions ) . ")";
        			$sortColumn = $sortColumn===""||$sortColumn===false?"first_name":$sortColumn;
        			$sortCriteria = $sortCriteria===""||$sortCriteria===false?"DESC":$sortCriteria;
        			$query .= "$conditions " ;
        		}
        	}
        	if( $sortColumn != "" || $sortColumn != false ){
        		$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"DESC";
        		$query .= " ORDER BY $sortColumn $sortCriteria " ;
        	}
        	$queryGetRow = $query ;
        	$total = ceil($this->getRowWithQuery($queryGetRow)/$perpage);
        	$query .= " LIMIT ".(($start-1)*$perpage)." , ".$perpage."; " ;
        	return array("page"=>$start,"total"=>$total,"users"=>$this->executeQueryAsObject( $query ));
        }

        public function displayRequest( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("first_name","last_name","email") , $sortColumn="first_name" , $sortCriteria="ASC" ){
            
            $start = is_numeric($start)&&$start>0?$start:1;
            $perpage = is_numeric($perpage)&&$perpage>0?$perpage:20;
            
            $result = array();
            $query = "SELECT users.id as user_id , first_name , last_name , email , type , label , price , start_date, end_date 
            FROM users , expiredates , userexpireds
            WHERE users.id=userexpireds.user_id
            AND   expiredates.id=userexpireds.expire_id  
            " ;
            if ( $searchString != "" ){
                $searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("first_name","last_name","email") : $searchColumns ; 
                if ( is_array($searchColumns) && !empty($searchColumns) ){
                $conditions = "" ;
                    $conditions = array() ;
                    foreach( $searchColumns AS $columnName ){
                        $conditions[] = " $columnName LIKE '%$searchString%' " ;
                    }
                    $conditions = " AND (" . implode("OR", $conditions ) . ") AND type = 1  ";
                    $sortColumn = $sortColumn===""||$sortColumn===false?"first_name":$sortColumn;
                    $sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
                    $query .= "$conditions " ;
                }
            }else{
                $query .= " AND type = 1 " ;
            }
            if( $sortColumn != "" || $sortColumn != false ){
                $sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"ASC";
                $query .= " ORDER BY $sortColumn $sortCriteria " ;
            }
            $queryGetRow = $query ;
            $total = ceil($this->getRowWithQuery($queryGetRow)/$perpage);
            $query .= " LIMIT ".(($start-1)*$perpage)." , ".$perpage."; " ;
            return array("page"=>$start,"total"=>$total,"users"=>$this->executeQueryAsObject( $query ));
        }

		public function add( $firstName , $lastName , $email , $gender , $phone , $address , $password , $type ){
		    $firstName = $this->sqlRealEscape($firstName);
		    $lastName = $this->sqlRealEscape($lastName);
		    $email = $this->sqlRealEscape($email);
            $address = $this->sqlRealEscape($address);
			return $this->executeQuery( "INSERT INTO users SET first_name='$firstName' , last_name='$lastName' , gender='$gender' , email='$email' , phone = '$phone' , address = '$address' , password='$password' , type='$type' ;");
		}
		public function edit( $id , $firstName , $lastName , $email , $gender , $phone , $address , $type ){
		    $firstName = $this->sqlRealEscape($firstName);
            $lastName = $this->sqlRealEscape($lastName);
            $email = $this->sqlRealEscape($email);
            $address = $this->sqlRealEscape($address);
            if($type == 2){
            	$ref = $this->getRef('Approval of Premium Membership','APM');// Approval of Premium Membership
            	$this->saveUserRefLog($ref, 'Approval of Premium Membership',$id);
            	return $this->executeQuery( "UPDATE users SET first_name='$firstName' , last_name='$lastName' , gender='$gender' , email='$email' , phone = '$phone' , address = '$address' , type='$type' WHERE id='$id';");
            }else
				return $this->executeQuery( "UPDATE users SET first_name='$firstName' , last_name='$lastName' , gender='$gender' , email='$email' , phone = '$phone' , address = '$address' , type='$type' WHERE id='$id';");
		}
		public function accountedit( $id , $firstName , $lastName , $gender , $phone ){
		    $firstName = $this->sqlRealEscape($firstName);
            $lastName = $this->sqlRealEscape($lastName);
			return $this->executeQuery( "UPDATE users SET first_name='$firstName' , last_name='$lastName' , gender='$gender' ,phone = '$phone' WHERE id='$id';");
		}
		public function editPassword($id,$password){
			return $this->executeQuery( "UPDATE users SET password='$password' WHERE id='$id';");
		}
		public function delete( $id ){
			$this->executeQuery( "DELETE FROM users WHERE id='$id';");
		}
		public function deleteActivity( $id ){
			$this->executeQuery( "DELETE FROM `user_reference_logs` WHERE id='$id';");
		}
		public function find( $id ){
			$query = "SELECT * FROM users WHERE id = '$id' ;" ;
			return $this->executeQueryAsObject($query);
		}
		public function checkmail($email){
			if ( $email != "" ){
				$query = "SELECT * FROM users WHERE email='$email' ";
				return $this->executeQueryAsObject( $query );
			}
			return null ;
		}
		public function setStringReset($id,$stringReset){			
		    $this->executeQuery( "UPDATE users SET reset='$stringReset' WHERE id='$id';");
		    $ref = $this->getRef('Request for Password Reset','RPW');// Reset Password Request
		    $this->saveUserRefLog($ref,'Request for Password Reset',$id);
		    return $ref;
		}
		public function getRequestPremium(){
			return $this->executeQueryAsObject("SELECT id , first_name , last_name , gender , email , password , type FROM users WHERE type=1 ;");
		}
		public function validate($username,$password){
			return $this->executeQueryAsObject("SELECT id , first_name , last_name , gender , email , password , type FROM users WHERE email = '".$username."' AND password ='".$password."' ;");
		}
        public function premiumUser(){
            $query = "SELECT * FROM users WHERE type=2 ;" ;
            return $this->executeQueryAsObject($query);
        }
		public function requestLevel($id,$level=0){
			$ref = $this->getRef('Request for Premium Membership', "RPM");// 1 = RPM = Request for Premium Membership
			$this->saveUserRefLog($ref,'Request for Premium Membership',$id);
			$query = "UPDATE users set type='$level' WHERE id='$id' ;";
			$this->executeQuery($query);
		}
		public function getRef($type = "", $pre = "",$increase = 1){			
			$order_number = 1;
			$sql = "SELECT ref_number FROM `user_reference_logs` WHERE `activities` = '" . $type . "' ORDER By `ref_number` DESC LIMIT 1";
			$result = $this->executeQueryAsObject($sql);//print_r($result);die();
			if(@$result[0]->ref_number != Null){
				$ref = $result[0]->ref_number;
				$order_number = substr(strstr($ref,"-"),1);
				if($order_number < 99999){
					$order_number +=$increase;
				}else{
					$order_number = 1;
				}
			}
			return $pre . "-" . sprintf("%05d",$order_number);
		}
		public function saveUserRefLog($ref = "", $type = "", $id = 0){
			date_default_timezone_set("Asia/Bangkok");
			$created = date('Y-m-d H:i:s');
			$modified = $created;
			$this->executeQuery( "INSERT INTO `user_reference_logs` SET `ref_number` = '" . $ref . "', `user_id` = " . $id . ", `activities` = '" . $type . "', `created` = '" . $created . "',`modified` ='" . $modified . "'" );
		}
		public function getRefResetPassword(){
			$pre = "RPW";// Reset Password
			$order_number = 1;
			$sql = "SELECT ref_number FROM `users` WHERE `reset` IS NOT NULL ORDER By `ref_number` DESC LIMIT 1";
			$result = $this->executeQueryAsObject($sql);
			if($result[0]->ref_number != ""){
				$ref = $result[0]->ref_number;
				$order_number = substr(strstr($ref,"-"),1);
				if($order_number < 99999){
					$order_number +=1;
				}else{
					$order_number = 1;
				}
			}
			return $pre . "-" . sprintf("%05d",$order_number);
		}
	}
?>